"""
@FINAL----FINAL----FINAL
@Uses: This file will be used to create training data title,
body, tags

Table is created using "get_Qid_initial_migration.py"
"""
import os
import MySQLdb
import re, urllib2
from collections import Counter
"""
file_name = "temp_sites"#"41_sites_name"
database = "isec2014"
data_table = "qimp_migration_124_41_aug13"
user = "root"
password="123"
port=3306
file_path2 = "parent_id" # These are the ids for which I am using their parent values
"""
file_name = "41_sites_name"
database = "sangeeta"
data_table = "qimp_migration_124_41_aug13"
user = "sangeetal"
password="sangeetal"
port=3307
file_path2 = "parent_id" # These are the ids for which I am using their parent values
#"""

#store_table = "qimp_vote_rep"
#store_table = @defined_below_in_the_code"
date = '2013-12-27'

db1= MySQLdb.connect(host="localhost", user=user, passwd=password,db=database, port=port)
select_cursor = db1.cursor()
select2_cursor = db1.cursor()
temp_cursor = db1.cursor()

#================================================================================
def clean_str(Body):
     Body = Body.replace("\n"," ")
     Body = Body.replace("'"," ")
     Body = Body.replace("\""," ")
     Body = Body.replace(",", " ")
     Body = Body.replace("(", " ")
     Body = Body.replace(")", " ")
     Body = Body.replace("\\", " ")
     return Body

def get_tags_and_tagcount(tag_str):
    number_of_tags = len(tag_str.split(">"))-1
    temp_tags = tag_str.split(">")
    tags = list()
    
    count = 1
    for t in temp_tags:
        if count<=number_of_tags:
            tags.append(t.split("<")[1])
            count = count+1
        
   # print "tags=", tags
    #count = 2
    return number_of_tags, tags  

def get_url_count(content):
    links = re.findall(r'(https?://\S+)', content, re.I)
    
    print links
    return len(links)  
def  get_first_capital_count(body_str):
    words = re.split('\s+', body_str)
    print words
    first_cap = list()
    for w in words:
        if w:
            if w[0].isupper() and len(w)>=2:
                first_cap.append(w)
    print "first_cap=", first_cap
    
    return len(first_cap) 
    
    
def  get_all_capital_count(body_str):
    words = re.split('\s+', body_str)
    #print words
    all_cap = list()
    for w in words:
        if w:
            if w.isupper() and len(w)>=2:
                all_cap.append(w)
    print "all cap=", all_cap            
    return len(all_cap) 
 
 
def is_popular_tag(initial_site, question_tags):
    str = "select tag from `tags_count_"+initial_site+"_aug13` order by count desc limit 0,100"
    #print "str = ", str
    temp_cursor = db1.cursor()
    temp_cursor.execute(str)
    temp_data = temp_cursor.fetchall()
    popular_tags = list()
    for p in temp_data:
        popular_tags.append(p[0])
    flag = 0
    
    q_tag = list()
    number_of_tags = len(question_tags.split(">"))-1
    temp_q_tags = question_tags.split(">")
    #temp_tags = tag_str.split(">")
    #tags = list()
    
    tag_count = 1
    for t in temp_q_tags:
        if tag_count<=number_of_tags:
            q_tag.append(t.split("<")[1])
            tag_count = tag_count+1
  
    for t in q_tag:
        try:
            index = popular_tags.index(t)
            flag = 1 
        except ValueError:
            #Do Nothing 
            flag = flag
    #print "populat tags=", popular_tags
    #print "question tag=", q_tag
    #print "flag =", flag
    #print "enter_str"
    #a = input()        
    return flag 

def body_have_code(body):
    try:
        body.index("<code>")
        return 1
    except ValueError:
        return 0

def count_of_special_chars(body):
    counter = Counter(body)   
    star = counter['*']
    dot  = counter['.']
    plus  =counter['+']
    open_brac =counter['[']
    close_brac = counter[']']
    for_slash = counter['/']
    back_slash = counter['\\']
    under_score =counter['_']
    hyphens  =counter['-']
    colon =counter[':']
    #print  "star = ", star 
    return star+dot+plus+open_brac+close_brac+for_slash+back_slash+under_score+hyphens       
#================================================================================

str1 = "select * from "+data_table +" where initial in \
('serverfault', 'stackoverflow', 'superuser', 'programmers.stackexchange',\
 'dba.stackexchange')"# limit 0,0"
 
select_cursor.execute(str1)

parent_file = open(file_path2,"wb")
migrated_q = select_cursor.fetchall()
count=0
for q in migrated_q:
    
    postid = (str)(q[3])
    migrated  =(str)( q[2])
    initial = (str)(q[1])
    question_id =(str) (q[0])
    
    post_table ="`posts_"+migrated+"_aug13`"
    users_table= "`users_"+migrated+"_aug13`"
        
   # str2   = "select id, score, view_count, answer_count, comment_count, owner_user_id, creation_date from " +post_table+" where id="+(str)(postid)+ " and posttypeid =1"
    str2   = "select id, score, view_count, answer_count, comment_count, owner_user_id,\
    creation_date, title, body, tags from " +post_table+" where id="+(str)(postid)+ \
    " and posttypeid =1"
   
    #print "str2=", str2
    select_cursor.execute(str2)
    data = select_cursor.fetchall()
    parent_id = "-1"
    
    if data:
        z=0#No use just to do something here
        print "true"
       
    else:
        print "false" 
        parent_str = "select parentid from "+post_table+" where id="+(str)(postid)   
        temp_cursor.execute(parent_str)
        parent_id = temp_cursor.fetchall()[0][0]
        #print "parent id =", parent_id
        new_str2   = "select id, score, view_count, answer_count, comment_count,\
        owner_user_id, creation_date, title, body, tags from "\
        +post_table+" where id="+(str)(parent_id)+ " and posttypeid =1"
        #print "new_str2=", new_str2
        select_cursor.execute(new_str2)
        data = select_cursor.fetchall()
        parent_file.write(postid+","+migrated+"\n")
        
    #print "data=",data
    id = (str)(data[0][0])
    score =(str)( data[0][1])
    view_count = (str)(data[0][2])
    answer_count = (str)(data[0][3])
    comment_count =(str)( data[0][4])
    owner_user_id =(str) (data[0][5])
    creation_date = (str) (data[0][6])
    f1_title = data[0][7]
    f1_body  = data[0][8]
    
    f1_title=clean_str(f1_title)
    f1_body=clean_str(f1_body)
    
    f1_title_len = len(f1_title)
    f1_body_len = len(f1_body)
    f1_url_count = get_url_count(f1_body)
    temp_tags = data[0][9] 
    f1_number_of_tags,tags = get_tags_and_tagcount(temp_tags)
    f1_all_capital_count=get_all_capital_count(f1_body)
    f1_first_capital_count=get_first_capital_count(f1_body)
    f1_have_popular_tag = is_popular_tag(initial,temp_tags)
    f1_special_char_count = count_of_special_chars(f1_body)
    f1_have_code = body_have_code(f1_body)
    str3= "select id, display_name, reputation, datediff('"+date+"',creation_date) \
    from "+users_table+" where id= "+owner_user_id
    select2_cursor.execute(str3)
    str3_data = select2_cursor.fetchall()
    #print "str3 data=", str3_data
    owner_display_name=(str)( str3_data[0][1])
    f1_reputation = (str)(str3_data[0][2])
    f1_account_age = (str)(str3_data[0][3])    
  
    
    #==================================================================
    print "post id = ",postid
    print "migrated = ", migrated
    print "question id = ", question_id
    print "initial =", initial
    print "score = ", score
    print "view count = ", view_count
    print "answer count=", answer_count
    print "comment count=", comment_count
    print "owner user id=", owner_user_id
    print "title =", f1_title
    print "body=", f1_body
    print "title len=", f1_title_len
    print "body len=", f1_body_len
    print "f1_url_count=", f1_url_count
    print "f1_number_of_tags=", f1_number_of_tags
    print "contains_popular = ", f1_have_popular_tag
    print "f1_all_capital_count=", f1_all_capital_count
    print "f1_first_capital_count=",f1_first_capital_count 
    print "f1_special chars count=", f1_special_char_count
    print"f1_have_code=", f1_have_code
    print "reputation = ", f1_reputation
    print "account age=", f1_account_age
     
    #==================================================================
    store_table="`train_migrated_"+initial+"_aug13`"
    insert_str = "insert into "+store_table+" values( "+ (str)(question_id)+",'"+initial+"','"+\
    migrated+"',"+(str)(postid)+","+(str)(score)+","+(str)(view_count)+","+\
    (str)(answer_count)+","+(str)(comment_count)+","+\
    (str)(owner_user_id)+",'"+owner_display_name+"','"+temp_tags+"','"+f1_title+"','"+\
    f1_body+"'," +(str)(f1_title_len)+","+\
    (str)(f1_body_len)+","+(str)(f1_url_count)+","+(str)(f1_number_of_tags)+","+\
    (str)(f1_have_popular_tag)+","+(str)(f1_all_capital_count)+","+\
    (str)(f1_first_capital_count) +","+(str)(f1_special_char_count)+","\
    +(str)(f1_have_code)+","+(str)(f1_reputation)+","+(str)(f1_account_age)+")"
    print "insert str = ", insert_str
    select_cursor.execute(insert_str)
    count=count+1
    print "count=", count
    
    """##Not a comment
    #print "reputation=", reputation
    
    insert_str = "insert into "+store_table  +" values("+question_id+",'"+initial+"','"+\
    migrated+"',"+postid +","+score+","+view_count+ ","+answer_count+","+\
    comment_count+","+owner_user_id+",'"+owner_display_name+"',"+reputation+",'"+creation_date+"')"
    
    #print "insert=", insert_str
    select_cursor.execute(insert_str)
    count=count+1
    print "count=", count
    """

db1.commit()
#parent_file.close()

print "done----- doen.... Now filling not migrated"

five_sites = [ 'serverfault',\
               'superuser','dba.stackexchange','programmers.stackexchange','stackoverflow']
#five_sites  = ['stackoverflow']@NOTE: Not working for stackoverflow
                                 #@Check: create_train_db_qimp_vote_rep_SO.py 
             
count1=0
for site in five_sites:
     post_table ="`posts_"+site+"_aug13`"
     users_table= "`users_"+site+"_aug13`"
     str2 = "select id, score, view_count, answer_count,comment_count,+\
     owner_user_id,creation_date,title, body, tags from "+post_table+ \
     "  where posttypeid = 1 and accepted_answer_id!=-1"
     select_cursor.execute(str2)
     all_data = select_cursor.fetchall()
     
     for data in all_data:
          print "data=", data
          id = (str)(data[0])
          postid = id
          migrated = "not migrated"
          initial =site
          question_id = id
          
          score =(str)( data[1])
          view_count = (str)(data[2])
          answer_count = (str)(data[3])
          comment_count =(str)( data[4])
          owner_user_id =(str) (data[5])
          creation_date = (str) (data[6])
          f1_title = data[7]
          f1_body  = data[8]
          
          f1_title=clean_str(f1_title)
          f1_body=clean_str(f1_body)
          
          f1_title_len = len(f1_title)
          f1_body_len = len(f1_body)
          f1_url_count = get_url_count(f1_body)
          temp_tags = data[9] 
          f1_number_of_tags,tags = get_tags_and_tagcount(temp_tags)
          f1_all_capital_count=get_all_capital_count(f1_body)
          f1_first_capital_count=get_first_capital_count(f1_body)
          f1_have_popular_tag = is_popular_tag(initial,temp_tags)
          f1_special_char_count = count_of_special_chars(f1_body)
          f1_have_code = body_have_code(f1_body)
          str3= "select id, display_name, reputation, datediff('"+date+"',creation_date) \
          from "+users_table+" where id= "+owner_user_id
          select2_cursor.execute(str3)
          str3_data = select2_cursor.fetchall()
          #print "str3 data=", str3_data
          owner_display_name=(str)( str3_data[0][1])
          f1_reputation = (str)(str3_data[0][2])
          f1_account_age = (str)(str3_data[0][3])    
  
    
          #==================================================================
          print "post id = ",postid
          print "migrated = ", migrated
          print "question id = ", question_id
          print "initial =", initial
          print "score = ", score
          print "view count = ", view_count
          print "answer count=", answer_count
          print "comment count=", comment_count
          print "owner user id=", owner_user_id
          print "title =", f1_title
          print "body=", f1_body
          print "title len=", f1_title_len
          print "body len=", f1_body_len
          print "f1_url_count=", f1_url_count
          print "f1_number_of_tags=", f1_number_of_tags
          print "contains_popular = ", f1_have_popular_tag
          print "f1_all_capital_count=", f1_all_capital_count
          print "f1_first_capital_count=",f1_first_capital_count 
          print "f1_special chars count=", f1_special_char_count
          print"f1_have_code=", f1_have_code
          print "reputation = ", f1_reputation
          print "account age=", f1_account_age
     
          #==================================================================
          store_table="`train_not_migrated_"+site+"_aug13`"
          insert_str = "insert into "+store_table+" values( "+ (str)(question_id)+",'"+initial+"','"+\
          migrated+"',"+(str)(postid)+","+(str)(score)+","+(str)(view_count)+","+\
          (str)(answer_count)+","+(str)(comment_count)+","+\
          (str)(owner_user_id)+",'"+owner_display_name+"','"+temp_tags+"','"+f1_title+"','"+\
          f1_body+"'," +(str)(f1_title_len)+","+\
          (str)(f1_body_len)+","+(str)(f1_url_count)+","+(str)(f1_number_of_tags)+","+\
          (str)(f1_have_popular_tag)+","+(str)(f1_all_capital_count)+","+\
          (str)(f1_first_capital_count) +","+(str)(f1_special_char_count)+","\
          +(str)(f1_have_code)+","+(str)(f1_reputation)+","+(str)(f1_account_age)+")"
          print "insert str = ", insert_str
          select_cursor.execute(insert_str)
          count1=count1+1
          print "count1=", count1
          
          
     db1.commit()  
   
###Not a comment"""